/**
 * 
 */
package com.dingding.order.dto.orderlist;

/**
 * @ClassName: NearbyOrderListSQL
 * @Description:附近订单列表统一SQL存放
 * @author zhouxuan
 * @date 2016年6月4日 下午8:24:55
 */

public class NearbyOrderListSQL {
	/**
	 * 根据用户Id获取用户启用的路线
	 */
	public static final String GET_ALL_ENABLE_USERROUTE = "SELECT u.urId,u.setOutTime,u.startLongitude,u.startLatitude,u.purposeLongitude,u.purposeLatitude,u.routeType FROM Userroute u WHERE u.isEnable = 1 AND u.loginId = ?";

	/**
	 * 乘客获取附近司机列表(无筛选条件) 1:智能排序 2:最早出发 3：离我最近 4：女士优先 5：男士优先
	 */
	public static final String PassengerGetNearByUserList(Integer sortCondition) {

		StringBuilder sBuilder = new StringBuilder();

		sBuilder.append(
				"SELECT ut.placeOrderUserId,ut.oid,ud.nickName,ud.userAvatar,ut.setOutTime,ut.startAddress,ut.startLongitude,ut.startLatitude,ut.endAddress,ut.endLongitude,ut.endLatitude,ut.totalMoney,ut.orderStatus,ut.redBag,ut.message,ut.number,ud.tellphone,ud.userTabloidAvatar,ud.sex,vi.lisencePlate,vi.vehicleModel,vi.vehicleType,vi.vehicleColor,ut.distance,ut.payCity,ut.isLongDistance,ud.userLevel,ut.isCarShare,ut.setOutCondition,ut.freeSeat FROM userordert ut INNER JOIN userdetail ud ON ut.placeOrderUserId = ud.loginId INNER JOIN vehicleinfo vi ON ut.placeOrderUserId = vi.loginId WHERE isChildren = 1 AND (ut.orderStatus = 1 OR (ut.orderStatus = 2 AND ut.isCarShare = 2 )) AND freeSeat > 0 AND ut.placeOrderUserId != ?");
		if (sortCondition == 4) { // 女士优先
			sBuilder.append(" order by ud.sex desc");
		} else if (sortCondition == 5) { // 男士优先
			sBuilder.append(" order by ud.sex asc");
		} else if (sortCondition == 1) { // 智能排序
			sBuilder.append(" order by ut.setOutTime asc");
		} else if (sortCondition == 2) { // 最早出发
			sBuilder.append(" order by ut.setOutTime asc");
		}

		sBuilder.append(" LIMIT ?,?");

		return sBuilder.toString();
	}

	/**
	 * 司机获取乘客附近列表(无筛选条件) 1:智能排序 2:最早出发 3：离我最近 4：女士优先 5：男士优先
	 */
	public static final String DriverGetNearByUserList(Integer sortCondition) {

		StringBuilder sBuilder = new StringBuilder();

		sBuilder.append(
				"SELECT ut.placeOrderUserId,ut.oid,ud.nickName,ud.userAvatar,ut.setOutTime,ut.startAddress,ut.startLongitude,ut.startLatitude,ut.endAddress,ut.endLongitude,ut.endLatitude,ut.totalMoney,ut.orderStatus,ut.redBag,ut.message,ut.number,ud.tellphone,ud.userTabloidAvatar,ud.sex,vi.lisencePlate,vi.vehicleModel,vi.vehicleType,vi.vehicleColor,ut.distance,ut.payCity,ut.isLongDistance,ud.userLevel,ut.isCarShare,ut.setOutCondition,ut.freeSeat FROM userordert ut INNER JOIN userdetail ud ON ut.placeOrderUserId = ud.loginId INNER JOIN vehicleinfo vi ON ut.placeOrderUserId = vi.loginId WHERE isChildren = 2 AND ut.orderStatus = 1 AND ut.placeOrderUserId != ?");
		if (sortCondition == 4) { // 女士优先
			sBuilder.append(" order by ud.sex desc");
		} else if (sortCondition == 5) { // 男士优先
			sBuilder.append(" order by ud.sex asc");
		} else if (sortCondition == 1) { // 智能排序
			sBuilder.append(" order by ut.setOutTime asc");
		} else if (sortCondition == 2) { // 最早出发
			sBuilder.append(" order by ut.setOutTime asc");
		}

		sBuilder.append(" LIMIT ?,?");

		return sBuilder.toString();
	}

	/**
	 * @Title: TaxiGetNearbyOrderList
	 * @Description:出租车获取附近订单列表
	 * @author zhouxuan
	 * @return String
	 */
	public static final String TaxiGetNearbyOrderList() {
		StringBuilder sBuilder = new StringBuilder();
		sBuilder.append(
				"SELECT utt.distance,utt.endAddress,utt.endLatitude,utt.endLongitude,utt.message,ut.nickName,utt.number,utt.taxiOrdertId,utt.orderStatus,utt.placeOrderUserId,utt.setOutTime,ut.sex,utt.startAddress,utt.startLatitude,utt.startLongitude,ut.tellphone,ut.userAvatar,ut.userTabloidAvatar FROM usertaxiordert utt INNER JOIN userdetail ut ON ut.loginId=utt.placeOrderUserId WHERE utt.orderStatus=1 AND utt.placeOrderUserId!=? AND utt.placeOrderCity like ?" );
		sBuilder.append(" LIMIT ?,?");
		return sBuilder.toString();
	}
}
